package com.sq.partner.manager.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.ITradNoCarProdPlanDao;
import com.sq.partner.manager.mybatis.model.DeptDto;
import com.sq.partner.manager.mybatis.model.NocarPlan;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;
@Repository
public class TradNoCarProdPlanDaoImpl extends BaseDaoImpl implements ITradNoCarProdPlanDao {
	private static Logger logger = LogUtil.getLog(TradNoCarProdPlanDaoImpl.class);

	@Override
	public NocarPlan getMaxPlanCodeFromDB(String planFlag) throws SQLException {
		
		return (NocarPlan) this.query("queryMaxPlanCodeFromTB", planFlag);
	}

	@Override
	public boolean addNocarPlan(NocarPlan nocarPlan) throws Exception {
		// TODO Auto-generated method stub
		return this.add("insertNocarPlan", nocarPlan);
	}

	@Override
	public Page<NocarPlan> queryNocarPlanPage(Page<NocarPlan> page) throws SQLException {

		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append(" SELECT T.PLAN_CODE AS PLANCODE,T.PLAN_NAME AS PLANNAME,T.PARTNER_CODE AS PARTNERCODE, w.prod_type as prodType, H.CHANNEL_NAME AS PARTNERDESC, T.PROD_CODE    AS PRODCODE,"
	        +"W.PROD_NAME    AS PRODNAME FROM T_NOCAR_PLAN T INNER JOIN T_NOCAR_PROD W ON T.PROD_CODE = W.PROD_CODE  INNER JOIN T_GATEWAY_CHANNEL_INFO H ON H.CHANNEL_CODE = T.PARTNER_CODE "
			+ "where 1=1 ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			NocarPlan ncp= page.getType();
			if(StrUtil.isNotEmpty(ncp.getPartnerCode())){
				sqlbuf.append(" AND t.partner_code  = ?");
				list.add(ncp.getPartnerCode());
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(ncp.getPlanCode())){
				sqlbuf.append(" AND t.plan_code like ?");
				list.add(ncp.getPlanCode());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(ncp.getPlanName())){
				sqlbuf.append(" AND t.plan_name like ?");
				list.add(ncp.getPlanName());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(ncp.getProdCode())){
				sqlbuf.append(" AND t.prod_code like ?");
				list.add(ncp.getProdCode());
				islike.add(true);
			}
			
/*			List<DeptDto> depts=page.getDepts();
			sqlbuf.append(" AND h.dept_code in (");
			String deept_code = "";
			if(null!=depts){
				StringBuffer deptbuf=new StringBuffer();
				for(int i=0;i<depts.size();i++){
					deptbuf.append("?,");
					deept_code = depts.get(i).getDeptCode().substring(0, 2);
					if(!"00".equals(deept_code)){
						list.add(deept_code+"%");
					}else{
						list.add(deept_code);
					}
					islike.add(false);
				}
				String deptstr=deptbuf.toString();
				deptstr=deptstr.substring(0, deptstr.length()-1);
				if("00".equals(deept_code)){
					sqlbuf.append("select dept_code from t_core_dept t connect by prior dept_code = snr_dept start with dept_code in ("+deptstr+")");
				}else{
					sqlbuf.append("SELECT dept_code FROM t_core_dept WHERE DEPT_CODE like '00%'");
					sqlbuf.append(" UNION ");
					sqlbuf.append("SELECT dept_code FROM t_core_dept WHERE DEPT_CODE like "+deptstr);
				}
				
			}else{
				sqlbuf.append("''");
			}
			sqlbuf.append(" )");
			if(!"00".equals(deept_code)){
				sqlbuf.append(" and t.agent_type != '6' ");
			}*/
			Map<String, Object> deptsMap=page.getMap();
			String depts=(String) deptsMap.get("depts");
			if("'00'".equals(depts)){
				sqlbuf.append(" AND h.dept_code in (");
				sqlbuf.append("select dept_code from t_core_dept t connect by prior dept_code = snr_dept start with dept_code ='00'");
				sqlbuf.append(" ) ");
			}else{
				sqlbuf.append("and ((h.DEPT_CODE in (SELECT dept_code FROM t_core_dept WHERE DEPT_CODE like '00%') AND"); 
				sqlbuf.append(" t.AGENT_TYPE = '4') or h.DEPT_CODE in (SELECT dept_code  FROM t_core_dept WHERE DEPT_CODE in ( "+depts+" ) ))");
					
			}
			sqlbuf.append(" order by NVL(t.update_time,t.insert_time) desc,t.plan_code  desc ");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	
	}

	@Override
	public NocarPlan queryTradNocarPlanInfo(NocarPlan nocarPlan)
			throws SQLException {
		// TODO Auto-generated method stub
		return (NocarPlan)this.query("queryNocarPlanInfoById", nocarPlan);
	}

	@Override
	public boolean updateTradNocarPlanInfo(NocarPlan nocarPlan)
			throws Exception {
		// TODO Auto-generated method stub
		return this.update("updateNocarPlan", nocarPlan);
	}

	@Override
	public boolean delTradNocarPlan(String planCode) throws Exception {
		// TODO Auto-generated method stub
		return this.delete("delNocarPlanById", planCode);
	}

	@Override
	public List<NocarPlan> queryTradNocarPlanByProdCode(String prodCode)
			throws SQLException {
		// TODO Auto-generated method stub
		return this.queryList("queryTradNocarPlanByProdCode", prodCode);
	} 
	
	@Override
	public boolean isHasExistPlan(String partnerNo) throws Exception {// TODO Auto-generated method stub
		Integer result = (Integer)this.query("queryNocarPlanByPartnerNo", partnerNo);
		if(null!=result &&  Integer.valueOf(result)>0)
		{
			return true;
		}
		return false;
	}

	@Override
	public Page<NocarPlan> queryPageForInsurePlanList(Page<NocarPlan> page,
			String channelCode) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("SELECT T1.PLANCODE, T1.PLANNAME from ( SELECT T.INSU_PLAN_NO AS PLANCODE, T.INSU_PLAN_NAME AS PLANNAME "
					+ " FROM T_TRAD_INSU_PLAN T WHERE T.PARTNER_NO = '"+channelCode+"' UNION ALL "
					+ " SELECT H.PLAN_CODE AS PLANCODE, H.PLAN_NAME AS PLANNAME FROM T_NOCAR_PLAN H WHERE "
					+ " H.PARTNER_CODE = '"+channelCode+"' UNION ALL SELECT T2.PLAN_NO, T2.PLAN_NAME FROM T_NLIFE_PLAN T2 "
					+ " WHERE T2.PARTNER_NO = '"+channelCode+"' ) t1 where 1=1 ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			NocarPlan plan= page.getType();
			if(StrUtil.isNotEmpty(plan.getPlanCode())){
				sqlbuf.append(" and t1.PLANCODE like ? ");
				list.add(plan.getPlanCode());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(plan.getPlanName())){
				sqlbuf.append(" and  T1.PLANNAME like ? ");
				list.add(plan.getPlanName());
				islike.add(true);
			}
			logger.info("----"+channelCode);
			
					
			sqlbuf.append("  ORDER BY t1.plancode");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Map<String,Object> queryMapTradNocarPlanInfo(NocarPlan nocarPlan)
			throws SQLException {

		return (Map<String, Object>) this.query("queryMapNocarPlanInfoById", nocarPlan);
	}

	@Override
	public  List<Map<String, Object>> searchNoCarPlansByplanCode(Map<String, Object> maps)
			throws Exception {
		// TODO Auto-generated method stub
		
		return this.queryList("searchNoCarPlansByplanCode", maps);
	}

	
}
